
package BeanManager;

import Beans.Note;
import Database.DBConnection;
import Database.PoolDatabaseManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Vector;

/**
 *
 * @author Raed Shomali
 */
public class NoteManager
{
    public static Note insertNote()
    {
        // Object to be returned ...
        Note note = null ;
        
        DBConnection connection = (DBConnection) PoolDatabaseManager.getConnection();

        // Insert Only When a Connection is available ..
        if ( connection != null )
        {
            try
            {
                // SQL Insert
                String sql = "insert into NOTES (DATE) " +
                        "values(STR_TO_DATE(?, '%m-%d-%Y') );";

                // For Better Performance Insert Statements
                PreparedStatement preparedStatement = (PreparedStatement) connection.prepareStatement( sql );

                // For Formatting Dates ...
                SimpleDateFormat dateFmt = new SimpleDateFormat( "MM-dd-yyyy" );

                // Replace the "?" with Values
                preparedStatement.setString( 1 , dateFmt.format( new Date() ) );

                // Do the insertion ...
                preparedStatement.executeUpdate();

                // --------------------
                // Getting The Object
                // --------------------
                
                sql = "select PK_ID , TITLE , MESSAGE , DATE from NOTES order by PK_ID desc;";

                // Efficient Query Statement
                preparedStatement = (PreparedStatement) connection.prepareStatement( sql );

                ResultSet rs = preparedStatement.executeQuery();

                // Check if a Row is returned ..
                if ( rs != null && rs.next() )
                {
                    Long noteID = rs.getLong( 1 );
                    String title = rs.getString( 2 );
                    String message = rs.getString( 3 );
                    Date publishDate = rs.getDate( 4 );

                    // Create a note
                    note = new Note();

                    // Set Note Parameters
                    note.setNoteID( noteID );
                    note.setTitle( title );
                    note.setMessage( message );
                    note.setPublishDate( publishDate );
                }
                
                // Release from memory ...
                preparedStatement.close();
            }
            catch ( SQLException e )
            {
                // Print any problems ...
                e.printStackTrace();
            }
            finally
            {
                // Disconnect
                PoolDatabaseManager.disconnect( connection );
            }
        }
        
        return note ;
    }

    public static boolean updateNote( Note note )
    {
        boolean isSuccessful = false;

        // If Note Exists
        if ( note != null )
        {
            DBConnection connection = (DBConnection) PoolDatabaseManager.getConnection();

            if ( connection != null )
            {
                try
                {
                    String sql = "update NOTES set " +
                            "TITLE = ?, " +
                            "MESSAGE = ?, " +
                            "DATE = STR_TO_DATE(?, '%m-%d-%Y') " +
                            "where PK_ID=?";

                    // Efficient Way of Updating
                    PreparedStatement preparedStatement = (PreparedStatement) connection.prepareStatement( sql );

                    // Replace "?" With Respective Values ..
                    preparedStatement.setString( 1 , note.getTitle() );
                    preparedStatement.setString( 2 , note.getMessage() );

                    SimpleDateFormat dateFmt = new SimpleDateFormat( "MM-dd-yyyy" );
                    preparedStatement.setString( 3 , dateFmt.format( note.getPublishDate() ) );

                    preparedStatement.setLong( 4 , note.getNoteID() );

                    // Update
                    preparedStatement.executeUpdate();

                    // Free From Memory
                    preparedStatement.close();

                    // Update Status
                    isSuccessful = true;
                }
                catch ( SQLException e )
                {
                    // Print Any Problems ..
                    e.printStackTrace();
                }
                finally
                {
                    // Disconnect
                    PoolDatabaseManager.disconnect( connection );
                }
            }
        }

        return isSuccessful;
    }

    public static boolean deleteNote( Note note )
    {
        boolean isSuccessful = false;

        // If Note Exists
        if ( note != null )
        {
            DBConnection connection = (DBConnection) PoolDatabaseManager.getConnection();

            // Check if connection exists
            if ( connection != null )
            {
                try
                {
                    String sql = "delete from NOTES where PK_ID=?";

                    // Efficent Deletion
                    PreparedStatement preparedStatement = (PreparedStatement) connection.prepareStatement( sql );

                    // Replace "?" With ID Value
                    preparedStatement.setLong( 1 , note.getNoteID() );

                    // Delete
                    preparedStatement.executeUpdate();

                    // Release From Memory
                    preparedStatement.close();

                    // Update Status
                    isSuccessful = true;
                }
                catch ( SQLException e )
                {
                    // Print any Problems ...
                    e.printStackTrace();
                }
                finally
                {
                    // Disconnect
                    PoolDatabaseManager.disconnect( connection );
                }
            }
        }

        return isSuccessful;
    }

    public static Note getNote( long noteID )
    {
        DBConnection connection = (DBConnection) PoolDatabaseManager.getConnection();

        // The Note Object to be returned
        Note note = null;

        // Check if a Connection is available ..
        if ( connection != null )
        {
            try
            {
                String sql = "select TITLE , MESSAGE , DATE from NOTES where PK_ID = ?";

                // Efficient Query Statement
                PreparedStatement preparedStatement = (PreparedStatement) connection.prepareStatement( sql );

                // Replace the "?" With the ID Value
                preparedStatement.setLong( 1 , noteID );

                ResultSet rs = preparedStatement.executeQuery();

                // Check if a Row is returned ..
                if ( rs != null && rs.next() )
                {
                    String title = rs.getString( 1 );
                    String message = rs.getString( 2 );
                    Date publishDate = rs.getDate( 3 );

                    // Create a note
                    note = new Note();

                    // Set Note Parameters
                    note.setNoteID( noteID );
                    note.setTitle( title );
                    note.setMessage( message );
                    note.setPublishDate( publishDate );
                }

                // Release From Memory
                preparedStatement.close();
            }
            catch ( SQLException e )
            {
                // Print any Problems ...
                e.printStackTrace();
            }
            finally
            {
                // Disconnect ..
                PoolDatabaseManager.disconnect( connection );
            }
        }

        return note;
    }

    public static Vector<Note> getAllNotes()
    {
        Vector<Note> notes = new Vector<Note>();

        DBConnection connection = (DBConnection) PoolDatabaseManager.getConnection();

        // Check if a Connection is available ..
        if ( connection != null )
        {
            try
            {
                String sql = "select PK_ID , TITLE , MESSAGE , DATE from NOTES";

                // Efficient Query Statement
                PreparedStatement preparedStatement = (PreparedStatement) connection.prepareStatement( sql );

                ResultSet rs = preparedStatement.executeQuery();

                // Loop through rows ..
                while ( rs != null && rs.next() )
                {
                    Long noteID = rs.getLong( 1 );
                    String title = rs.getString( 2 );
                    String message = rs.getString( 3 );
                    Date publishDate = rs.getDate( 4 );

                    // Create a note
                    Note note = new Note();

                    // Set Note Parameters
                    note.setNoteID( noteID );
                    note.setTitle( title );
                    note.setMessage( message );
                    note.setPublishDate( publishDate );

                    notes.add( note );
                }

                // Release From Memory
                preparedStatement.close();
            }
            catch ( SQLException e )
            {
                // Print any Problems ...
                e.printStackTrace();
            }
            finally
            {
                // Disconnect ..
                PoolDatabaseManager.disconnect( connection );
            }
        }

        return notes;
    }

}
